using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using VisualDB.Entities;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Text;
using System.Diagnostics;

namespace VisualDB.DAL
{
    public class SQLServerDAL : BaseDAL, IDatabaseDAL
    {
        private DatabaseEntity _databaseEntity;
        private SqlConnection _cnn;
        private SqlTransaction _transaction;
        private string _connectionString;

        public SQLServerDAL(DatabaseEntity databaseEntity)
        {
            _databaseEntity = databaseEntity;

            _connectionString = string.Format("Data Source={0};User Id={1};Password={2};", databaseEntity.ServerName, databaseEntity.Username, databaseEntity.Password);

            if (databaseEntity.UseWindowsAuthentication)
                _connectionString += "Integrated Security=SSPI;";

        }

        #region IDatabaseDAL Members

        public DatabaseEntity DatabaseEntity
        {
            get
            {
                return _databaseEntity;
            }

            set
            {
                _databaseEntity = value;
            }
        }

        public List<DatabaseEntity> GetDatabases(out string errors)
        {
            List<DatabaseEntity> databases = new List<DatabaseEntity>();

            errors = "";

            try
            {
                using (SqlConnection cnn = new SqlConnection(this._connectionString))
                {
                    cnn.Open();

                    SqlCommand cmd = new SqlCommand("sp_databases", cnn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        DatabaseEntity database = new DatabaseEntity();
                        database.DatabaseType = DatabaseType.GetDatabaseType(DatabaseType.SQLServer);
                        database.ServerName = this.DatabaseEntity.ServerName;
                        database.DatabaseName = reader.GetString(0);
                        database.Username = this.DatabaseEntity.Username;
                        database.Password = this.DatabaseEntity.Password;
                        database.UseWindowsAuthentication = this.DatabaseEntity.UseWindowsAuthentication;
                        databases.Add(database);
                    }

                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                errors = ex.Message;
            }

            return databases;
        }

        public bool IsOpened
        {
            get
            {
                if (_cnn != null)
                    return (_cnn.State == ConnectionState.Open);
                else
                    return false;
            }
        }

        public void Open()
        {
            _cnn = new SqlConnection(_connectionString);
            _cnn.Open();
            _cnn.ChangeDatabase(this.DatabaseEntity.DatabaseName);
            _cnn.InfoMessage += new SqlInfoMessageEventHandler(_cnn_InfoMessage);
        }

        public void BeginTransaction()
        {
            _transaction = _cnn.BeginTransaction();
        }

        public void RollbackTransaction()
        {
            _transaction.Rollback();
        }

        public void CommitTransaction()
        {
            _transaction.Commit();
        }

        void _cnn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            foreach (SqlError error in e.Errors)
            {
                Debug.WriteLine(error.ToString());
            }
        }

        public void Close()
        {
            _cnn.Close();
        }

        public List<TableEntity> GetTables()
        {
            List<TableEntity> tables = new List<TableEntity>();

            SqlCommand cmd = new SqlCommand("Select Table_Name from INFORMATION_SCHEMA.Tables where Table_Type='BASE TABLE' and TABLE_CATALOG=@Schema Order By Table_Name", _cnn);
            cmd.Parameters.AddWithValue("@Schema", this.DatabaseEntity.DatabaseName);
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                TableEntity table = new TableEntity();
                table.Name = reader.GetString(0);
                tables.Add(table);
            }

            reader.Close();

            return tables;
        }

        public string RenameTable(string oldName, string newName)
        {
            return string.Format("sp_rename [{0}], [{1}]", oldName, newName);
        }

        public string DeleteTable(TableEntity table)
        {
            return string.Format("DROP TABLE [{0}]", table.Name);
        }

        public string SaveTable(bool newTable, TableEntity table, List<ColumnEntity> originalColumns, List<ColumnEntity> newColumns)
        {
            return base.GenerateScriptSaveTable(DatabaseType.GetDatabaseType(DatabaseType.SQLServer), newTable, table, originalColumns, newColumns, "ADD", "ALTER COLUMN", "DROP");
        }

        public List<ColumnEntity> GetColumns(TableEntity table)
        {
            return GetColumnsInternal(table);
        }

        public List<ColumnEntity> GetColumns(ViewEntity view)
        {
            return GetColumnsInternal(view);
        }

        private List<ColumnEntity> GetColumnsInternal(object tableView)
        {
            string tableViewName;

            if (tableView is TableEntity)
            {
                tableViewName = ((TableEntity)tableView).Name;
            }
            else
            {
                tableViewName = ((ViewEntity)tableView).Name;
            }

            List<ColumnEntity> columns = new List<ColumnEntity>();

            SqlCommand cmd = new SqlCommand(string.Format("SELECT C.COLUMN_NAME, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH, C.IS_NULLABLE,  CPK.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.COLUMNS C LEFT JOIN (SELECT K.TABLE_NAME, K.COLUMN_NAME, TC.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON (K.TABLE_NAME=TC.TABLE_NAME AND K.CONSTRAINT_NAME=TC.CONSTRAINT_NAME) WHERE (K.TABLE_CATALOG=@Schema AND K.TABLE_NAME = @TableName AND TC.CONSTRAINT_TYPE='PRIMARY KEY')) CPK ON (C.TABLE_NAME=CPK.TABLE_NAME AND C.COLUMN_NAME=CPK.COLUMN_NAME) WHERE (C.TABLE_CATALOG=@Schema AND C.TABLE_NAME = @TableName) ORDER BY C.ORDINAL_POSITION", tableViewName), _cnn);
            cmd.Parameters.AddWithValue("@Schema", this.DatabaseEntity.DatabaseName);
            cmd.Parameters.AddWithValue("@TableName", tableViewName);

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                ColumnEntity column = new ColumnEntity();
                column.Id = Guid.NewGuid().ToString();
                column.Name = reader.GetString(0);

                string type = reader.GetString(1);
                object maxChars = reader.GetValue(2);

                if (maxChars != DBNull.Value)
                    column.Type = type + "(" + maxChars.ToString() + ")";
                else
                    column.Type = type;

                string allowNulls = reader.GetString(3);
                column.AllowNulls = allowNulls == "YES" ? true : false;

                object primaryKey = reader.GetValue(4);

                column.PrimaryKey = (primaryKey != null && primaryKey.ToString() == "PRIMARY KEY") ? true : false;

                columns.Add(column);
            }

            reader.Close();

            return columns;
        }

        public List<ViewEntity> GetViews()
        {
            List<ViewEntity> views = new List<ViewEntity>();

            SqlCommand cmd = new SqlCommand("Select Table_Name from INFORMATION_SCHEMA.VIEWS where TABLE_CATALOG=@Schema Order By Table_Name", _cnn);
            cmd.Parameters.AddWithValue("@Schema", this.DatabaseEntity.DatabaseName);

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                ViewEntity view = new ViewEntity();
                view.Name = reader.GetString(0);
                views.Add(view);
            }

            reader.Close();

            return views;
        }

        public string RenameView(string oldName, string newName)
        {
            return string.Format("sp_rename [{0}],[{1}]", oldName, newName);
        }

        public string DeleteView(ViewEntity view)
        {
            return string.Format("DROP VIEW [{0}]", view.Name);
        }

        public List<StoredProcedureEntity> GetStoredProcedures()
        {
            List<StoredProcedureEntity> storedProcedures = new List<StoredProcedureEntity>();

            SqlCommand cmd = new SqlCommand("Select ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_CATALOG=@Schema Order By ROUTINE_NAME", _cnn);
            cmd.Parameters.AddWithValue("@Schema", this.DatabaseEntity.DatabaseName);

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                StoredProcedureEntity storedProcedure = new StoredProcedureEntity();
                storedProcedure.Name = reader.GetString(0);
                storedProcedures.Add(storedProcedure);
            }

            reader.Close();

            return storedProcedures;
        }

        public string RenameStoredProcedure(string oldName, string newName)
        {
            string definition = GetStoredProcedureDefinition(oldName);

            definition = definition.Replace(oldName, newName);

            return string.Format("DROP PROCEDURE [{0}];\r\n{1}", oldName, definition);
        }

        public string DeleteStoredProcedure(StoredProcedureEntity storedProcedure)
        {
            return string.Format("DROP PROCEDURE [{0}]", storedProcedure.Name);
        }

        private string GetViewDefinition(string viewName)
        {
            SqlCommand cmd = new SqlCommand("Select VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS where (TABLE_CATALOG=@Schema AND TABLE_NAME=@ViewName)", _cnn);
            cmd.Parameters.AddWithValue("@Schema", this.DatabaseEntity.DatabaseName);
            cmd.Parameters.AddWithValue("@ViewName", viewName);

            return cmd.ExecuteScalar().ToString();
        }

        private string GetStoredProcedureDefinition(string storedProcedureName)
        {
            SqlCommand cmd = new SqlCommand("Select ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES where (ROUTINE_CATALOG=@Schema AND ROUTINE_NAME=@StoredProcedureName)", _cnn);
            cmd.Parameters.AddWithValue("@Schema", this.DatabaseEntity.DatabaseName);
            cmd.Parameters.AddWithValue("@StoredProcedureName", storedProcedureName);

            return cmd.ExecuteScalar().ToString();
        }

        public DataSet ExecuteQuery(string query, out string errors)
        {
            DataSet ds = null;
            errors = "";

            string[] queries = query.Split(';');

            try
            {
                BeginTransaction();

                ds = new DataSet();

                foreach (string sql in queries)
                {
                    SqlCommand cmd = new SqlCommand(sql, _cnn, _transaction);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(ds);
                }

                CommitTransaction();
            }
            catch (Exception ex)
            {
                errors = ex.Message;
                ds = null;
                RollbackTransaction();
            }

            return ds;
        }

        public int ExecuteCommand(string query, out string errors)
        {
            errors = "";
            int retVal = 0;

            string[] queries = query.Split(';');

            try
            {
                BeginTransaction();

                foreach (string sql in queries)
                {
                    SqlCommand cmd = new SqlCommand(sql, _cnn, _transaction);
                    retVal += cmd.ExecuteNonQuery();
                }

                CommitTransaction();
            }
            catch (Exception ex)
            {
                errors = ex.Message;
                RollbackTransaction();
            }

            return retVal;
        }


        public string GenerateScriptTable(ScriptType scriptType, TableEntity table)
        {
            return GenerateScriptTable(scriptType, table, this.GetColumns(table), "@");
        }

        public string GenerateScriptAddEditView(ViewEntity view, bool newView)
        {
            if (newView)
            {
                return string.Format("CREATE VIEW [{0}] AS\r\n", view.Name);
            }
            else
            {
                string definition = GetViewDefinition(view.Name);
                return string.Format("DROP VIEW [{0}];\r\n{1}", view.Name, definition);
            }
        }

        public string GenerateScriptSelectView(ViewEntity view)
        {
            return base.GenerateScriptSelectView(view, this.GetColumns(view));
        }

        public string GenerateScriptAddEditStoredProcedure(StoredProcedureEntity storedProcedure, bool newStoredProcedure)
        {
            if (newStoredProcedure)
            {
                return string.Format("CREATE PROCEDURE [{0}] AS\r\n", storedProcedure.Name);
            }
            else
            {
                string definition = GetStoredProcedureDefinition(storedProcedure.Name);
                return string.Format("DROP PROCEDURE [{0}];\r\n{1}", storedProcedure.Name, definition);
            }
        }

        public string GenerateScriptExecuteStoredProcedure(StoredProcedureEntity storedProcedure)
        {
            return string.Format("EXEC [{0}]", storedProcedure.Name);
        }

        #endregion
    }
}
